package org.codeblue.support.db;

import org.codeblue.support.utils.Maps;
import org.beetl.core.Configuration;
import org.beetl.core.GroupTemplate;
import org.beetl.core.Template;
import org.beetl.core.resource.FileResourceLoader;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * DE数据插入工具
 * 
 * @author Hongten
 * @mail hongtenzone@foxmail.com
 * @create 2013-8-3
 */
public class DataElementConfigTool {

	public static void main(String[] args) throws IOException {
		// 设置数据库链接信息
		DataBaseBO dataBaseBO = new DataBaseBO();
		dataBaseBO.setIp("10.34.41.243");
		dataBaseBO.setPort("3306");
		dataBaseBO.setDbName("wxculture2");
		dataBaseBO.setDriver("com.mysql.jdbc.Driver");
		dataBaseBO.setUserName("root");
		dataBaseBO.setPasswrod("Welcome123");
		dataBaseBO.setTableName("'tcafe'");
		dataBaseBO.setSql(dataBaseBO.getSelectSQL() + dataBaseBO.getTableName());
	//	System.out.println(dataBaseBO.getSql());
		// 初始化数据库链接的相关信息
		DataElementConfigTool tool = new DataElementConfigTool(dataBaseBO);
		// 数据库表结构情况
		List<TableDescBO> list = tool.getTableDescBOList(dataBaseBO);
		System.out.println(" Field     Type     Null     Key     Default     Extra      memo");
		List<TableColumn> columnList = new ArrayList<TableColumn>();
		if (list != null) {
			for (TableDescBO bo : list) {
				// System.out.println(bo.toString());
//				// 对数据库表描述进行封装成DataElementConfigBO对象
//				DataElementConfigBO decBo = tool.getDataElementConfigBO(bo, "gnzy");
//				// 向数据库表：data_element_config中插入数据
//				int result = tool.insertIntoDECTable(dataBaseBO, decBo);
//				System.out.println("插入数据：" + (result == 1 ? "成功" : "失败"));
				TableColumn  column = new TableColumn();
				column.setName(bo.getField().toLowerCase());
				column.setType(bo.getType());
				column.setDesc(bo.getMemo());

				columnList.add(column);
			}

		}


		Map javaEntity = Maps.toMap("tableName","tcafe" ,"className" ,"Cafe","fields",columnList);

		String root = "E:/Cluture_code/trunk/culture-support/src/main/java/com/sicent/support/api/doc";
		FileResourceLoader resourceLoader = new FileResourceLoader(root,"utf-8");
		Configuration cfg = Configuration.defaultConfiguration();
		GroupTemplate gt = new GroupTemplate(resourceLoader, cfg);
		Template t = gt.getTemplate("/entity.tpl");

		Map param = Maps.toMap("package","com.sicent.culture.entity","entity",javaEntity);
		t.binding(param);

//		String str = t.renderTo();
//		System.out.println(str);

		t.renderTo(new FileOutputStream(new File("E:\\Cluture_code\\trunk\\culture-service\\src\\main\\java\\com\\sicent\\culture\\entity\\Cafe.java")));

	}


	private String sqlType2JavaType(String sqlType) {

        if (sqlType.equalsIgnoreCase("bit")) {
            return "boolean";
        } else if (sqlType.equalsIgnoreCase("tinyint")) {
            return "byte";
        } else if (sqlType.equalsIgnoreCase("smallint")) {
            return "short";
        } else if (sqlType.equalsIgnoreCase("int")) {
            return "int";
        } else if (sqlType.equalsIgnoreCase("bigint")) {
            return "long";
        } else if (sqlType.equalsIgnoreCase("float")) {
            return "float";
        } else if (sqlType.equalsIgnoreCase("decimal") || sqlType.equalsIgnoreCase("numeric")
                || sqlType.equalsIgnoreCase("real") || sqlType.equalsIgnoreCase("money")
                || sqlType.equalsIgnoreCase("smallmoney")) {
            return "double";
        } else if (sqlType.equalsIgnoreCase("varchar") || sqlType.equalsIgnoreCase("char")
                || sqlType.equalsIgnoreCase("nvarchar") || sqlType.equalsIgnoreCase("nchar")
                || sqlType.equalsIgnoreCase("text")) {
            return "String";
        } else if (sqlType.equalsIgnoreCase("datetime")) {
            return "Date";
        } else if (sqlType.equalsIgnoreCase("image")) {
            return "Blod";
        }

        return null;
    }

	/**
	 * 初始化数据库链接的相关信息
	 * 
	 * @param dataBaseBO
	 *            数据库配置信息
	 */
	public DataElementConfigTool(DataBaseBO dataBaseBO) {
		super();
		dataBaseBO.setIp(dataBaseBO.getIp() == null ? "localhost" : dataBaseBO.getIp());
		dataBaseBO.setPort(dataBaseBO.getPort() == null ? "3306" : dataBaseBO.getPort());
		dataBaseBO.setUrl("jdbc:mysql://" + dataBaseBO.getIp() + ":" + dataBaseBO.getPort() + "/" + dataBaseBO.getDbName());
	}

	/**
	 * 数据库表结构情况
	 * 
	 * @param dataBaseBO
	 *            数据库配置信息
	 * @return 所需查询的数据表的字段信息
	 */
	public List<TableDescBO> getTableDescBOList(DataBaseBO dataBaseBO) {
		List<TableDescBO> list = new ArrayList<TableDescBO>();
		TableDescBO tableDescBO = null;
		try {
			Class.forName(dataBaseBO.getDriver());
			Connection conn = DriverManager.getConnection(dataBaseBO.getUrl(), dataBaseBO.getUserName(), dataBaseBO.getPasswrod());
//			PreparedStatement ps = conn.prepareStatement(dataBaseBO.getSql());
			String sql = "SELECT   column_name as field, data_type as type, column_comment as memo,is_nullable as isNullable  FROM Information_schema.columns WHERE TABLE_SCHEMA='wxculture2' AND table_Name = 'tcafe';" ;
			PreparedStatement ps = conn.prepareStatement(sql);

			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {

				String f = rs.getString("field");
//				System.out.println(f);
				tableDescBO = new TableDescBO();
				tableDescBO.setField(rs.getString(1));
				tableDescBO.setType(rs.getString(2));
				tableDescBO.setMemo(rs.getString(3));
//				tableDescBO.setMunericLength(rs.getString(4));
//				tableDescBO.setNumericScale(rs.getString(5));
//				tableDescBO.setIsNullable(rs.getString(6));
//				tableDescBO.setExtra(rs.getString(7));
//				tableDescBO.setIsDefault(rs.getString(8));
//				tableDescBO.setCharacterLength(rs.getString(9));
				list.add(tableDescBO);
			}
			close(rs, ps, conn);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 执行向数据库表:<code>data_element_config</code>中插入数据
	 * 
	 * @param dataBaseBO
	 *            数据库配置信息
	 * @param decBo
	 *            data_element_config这张表的BO类
	 * @return 返回:<code>-1</code>, 表示插入数据失败，否则成功
	 */
	public int insertIntoDECTable(DataBaseBO dataBaseBO, DataElementConfigBO decBo) {
		int result = -1;
		if (decBo != null) {
			String sql = decBo.getInsertIntoSQL() + decBo.getDeName() + "," + decBo.getDeGroup() + "," + decBo.getMemo() + "," + decBo.getDataType() + "," + decBo.getValueCheck() + "," + decBo.getYxBj() + ")";
			try {
				Class.forName(dataBaseBO.getDriver());
				Connection conn = DriverManager.getConnection(dataBaseBO.getUrl(), dataBaseBO.getUserName(), dataBaseBO.getPasswrod());
				PreparedStatement ps = conn.prepareStatement(sql);
				result = ps.executeUpdate();
				close(null, ps, conn);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return result;
	}

	/**
	 * 去除括号，如："int(11)",去除括号了以后，为："int"
	 * 
	 * @param oldType
	 * @return
	 */
	public static String getType(String oldType) {
		if (oldType != null && !oldType.equals("")) {
			return oldType.substring(0, oldType.indexOf("("));
		}
		return null;
	}

	/**
	 * 对数据库表描述进行封装成DataElementConfigBO对象
	 * 
	 * @param tableDescBO
	 *            数据库表的描述
	 * @param group
	 *            字段的分组名称，在表：<code>data_element_config</code>中对应的
	 *            <code>de_group</code>字段
	 * @return dataElementConfig对象的一个实例
	 */
	public DataElementConfigBO getDataElementConfigBO(TableDescBO tableDescBO, String group) {
		DataElementConfigBO bo = null;
		if (tableDescBO != null) {
			bo = new DataElementConfigBO();
			bo.setDeName("'" + tableDescBO.getField() + "'");
			bo.setDeGroup("'" + group + "'");
			bo.setValueCheck("'true'");
			bo.setYxBj("'1'");
			bo.setMemo("'" + tableDescBO.getMemo() + "'");
			bo.setDataType(1);
		}
		return bo;
	}

	/**
	 * 关闭数据库的相关链接
	 * 
	 * @param rs
	 *            记录集
	 * @param ps
	 *            声明
	 * @param conn
	 *            链接对象
	 */
	public void close(ResultSet rs, PreparedStatement ps, Connection conn) {
		// 关闭记录集
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		// 关闭声明
		if (ps != null) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		// 关闭链接对象
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}
